import pandas as pd
import numpy as np
import time
import warnings
import os
from openpyxl import load_workbook
import pyperclip
warnings.simplefilter('ignore')

# 清洗类
class Datacleansing:
    # 对原始的网点客诉工单表做序列抽取
    # filepath0:要做序列抽取的文件路径
    def sequence_extract(self, work_order):
        # 提取出需要的列
        work_order_data = {'申报时间': work_order['申报时间'],
                           '运单号': work_order['运单号'],
                           '三级工单类型': work_order['三级工单类型'],
                           '承包商': work_order['派件业务员'],
                           '派件网点': work_order['派件网点'],
                           '工单来源': work_order['工单来源'],
                           '当前节点': work_order['当前节点'],
                           '处理超时时间': work_order['处理超时时间']}
        return pd.DataFrame(work_order_data)

    # 将原始的网点客诉工单按发件网点为布吉的剔除掉
    # work_order:要做筛选的数据对象
    def filter_branch(self, work_order):
        # 声明一个计数变量 作用:记住发件网点为深圳布吉的行号
        num = 0
        # 声明一个数组当中容器 作用:将行号存放在数组中,为后面删除提供行号
        nums = []
        # 判断发件网点是否为布吉,记录行号,再按行号删除
        for i in work_order['发件网点']:
            if i == '深圳布吉':
                nums.append(num)
            num += 1
        return work_order.drop(work_order.index[nums])

    # 单纯的判断派件网点
    # filepath:要判断的文件
    def estimate_branch(self, filepath):
        for i in filepath['派件网点']:
            if i == '深圳布吉':
                return 1
            else:
                return 0

    # 将运单状态表和清洗过的工单客诉表匹配承包商,再将匹配后的数据和签收表匹配签收人,再将匹配后的数据和区域匹配表匹配主管经理
    # work_order1:签收表路径
    # work_order2:区域匹配表路径
    # work_order:清洗完后的客诉工单对象
    def matching_contractor(self, work_order1, work_order2, work_order3, work_order4):
        work_order = pd.merge(work_order4, work_order1, how='left')
        work_order = pd.merge(work_order, work_order2, how='left')
        work_order = pd.merge(work_order, work_order3, how='left')
        work_order = work_order.drop_duplicates(subset=['运单号', '回访结果'], keep='first')
        return work_order

    # 将表中的运单号复制到粘贴板中
    # work_order0:清洗后的布吉客诉工单表对象
    # work_order1:清洗后的吉华客诉工单表对象
    # work_order2:清洗后的南湾客诉工单表对象 （三个可以不用按顺序传参）
    def copy_odd(self, work_order0, work_order1, work_order2):
        # 对每个对象的列都遍历一遍,在将每个单号都隔行拼接起来
        w0 = ''
        w1 = ''
        w2 = ''
        for j in work_order0['运单号']:
            w0 += '\r\n' + str(j)

        for j in work_order1['运单号']:
            w1 += '\r\n' + str(j)

        for j in work_order2['运单号']:
            w2 += '\r\n' + str(j)
        w = w0+w1+w2
        # 将字符串中的文本复制到剪切板
        pyperclip.copy(w)
        pyperclip.paste()

    # 创建文件目录,如有同名目录不创建,没有同名目录就创建目录
    def Creating_file_Directory(self):
        path = "D:/ExcelData"
        if not os.path.exists(path):
            os.makedirs(path)

    # 读取签收表
    def read_sign(self, filepath):
        return pd.read_excel(filepath, dtype={'单号': str},
                                       engine='openpyxl',
                                       usecols=['单号', '签收人']).rename(columns={'单号': '运单号'})

    # 读取匹配表
    def read_matching(self, filepath):
        return pd.read_excel(filepath, dtype={'名称': str},
                                       usecols=['名称', '区域主管']).rename(columns={'名称': '承包商', '区域主管': '区域主管'})

    # 读取回访结果表
    def read_review_the_results(self, filepath, filepath1, filepath2):
        a0 = pd.read_excel(filepath, dtype={'运单号': str},
                                     usecols=['运单号', '回访结果'])

        a1 = pd.read_excel(filepath1, dtype={'运单号': str},
                                      usecols=['运单号', '回访结果'])

        a2 = pd.read_excel(filepath2, dtype={'运单号': str},
                                      usecols=['运单号', '回访结果'])

        ax0 = pd.DataFrame(a0)
        ax1 = pd.DataFrame(a1)
        ax2 = pd.DataFrame(a2)
        axx0 = ax0.append(ax1, ignore_index=True)
        axxx0 = axx0.append(ax2, ignore_index=True)
        return axxx0

    # 提取最终要的序列数据,并生成excel文件
    def compound_data(self, work_order):
        # 读取需要的列,并将派件业务员字段名 改成 承包商
        self.Creating_file_Directory()
        work_order_data = {'申报时间': work_order['申报时间'],
                           '运单号': work_order['运单号'],
                           '三级工单类型': work_order['三级工单类型'],
                           '承包商': work_order['承包商'],
                           '派件网点': work_order['派件网点'],
                           '签收人': work_order['签收人'],
                           '工单来源': work_order['工单来源'],
                           '区域主管': work_order['区域主管'],
                           '当前节点': work_order['当前节点'],
                           '处理超时时间': work_order['处理超时时间'],
                           '回访结果': work_order['回访结果']
                           }
        path1 = "D:/ExcelData/工单客诉"+str(time.time())+".xlsx"
        pdf = pd.DataFrame(work_order_data).sort_values(by='区域主管').sort_values(by='承包商').sort_values(by='三级工单类型')
        pdf = pdf.drop_duplicates()
        pdf.to_excel(path1, index=False)
        return path1

    # 读取 原始网点客诉工单表 并初步清洗 最后将三个网点表内工单一起复制到鼠标粘贴板
    def read_three_excel(self, file1, file2, file3):
        try:
            a0 = pd.read_excel(file1, dtype={'运单号': str})
            a1 = pd.read_excel(file2, dtype={'运单号': str})
            a2 = pd.read_excel(file3, dtype={'运单号': str})
            a0 = self.sequence_extract(self.filter_branch(a0))
            a1 = self.sequence_extract(self.filter_branch(a1))
            a2 = self.sequence_extract(self.filter_branch(a2))
            ax = [a0, a1, a2]
            self.copy_odd(a0, a1, a2)
            return ax
        except Exception:
            return False

    # 生成清洗匹配后的各网点客诉工单表
    def summarize(self, file1, file2, file3, file5, file6, file7, file8, file9):
        try:
            a4 = self.read_sign(file5)
            a5 = self.read_matching(file6)
            a3 = self.read_review_the_results(file7, file8, file9)
            s1 = self.compound_data(self.matching_contractor(a4, a5, a3, self.read_three_excel(file1, file2, file3)[0]))
            s2 = self.compound_data(self.matching_contractor(a4, a5, a3, self.read_three_excel(file1, file2, file3)[1]))
            s3 = self.compound_data(self.matching_contractor(a4, a5, a3, self.read_three_excel(file1, file2, file3)[2]))
            # 方法1：通过start explorer
            # os.system(folder)
            # 方法2：通过startfile
            os.startfile(s1)
            os.startfile(s2)
            os.startfile(s3)
            return True
        except Exception:
            return False

    # 数据透视 并打开添加透视表后生成的文件
    def data_pivot(self, filepath0, filepath1, filepath2):
        try:
            pdd = [filepath0, filepath1, filepath2]
            for i in pdd:
                pdr = pd.read_excel(i, dtype={'运单号': str})
                pd0 = pd.pivot_table(pdr, index='承包商', values='运单号', aggfunc=np.count_nonzero, margins=True, margins_name='总计')
                pda = pd0.sort_values(by='运单号', ascending=False)
                pd1 = np.shape(pdr)[1]+2
                book = load_workbook(i)
                write = pd.ExcelWriter(i, engine='openpyxl')
                write.book = book
                write.sheets = {ws.title: ws for ws in book.worksheets}
                pda.to_excel(write, sheet_name='Sheet1', startrow=0, startcol=pd1)
                write.close()
            os.startfile(pdd[0])
            os.startfile(pdd[1])
            os.startfile(pdd[2])
            return pdd
        except Exception:
            return False

    # 汇总透视  -- 生成汇总倒数排名并匹配主管经理
    def perspective_data(self, filepath0, filepath1, filepath2, filepath3):
        try:
            px0 = pd.read_excel(filepath3)
            pd0 = pd.read_excel(filepath0, dtype={'运单号': str})
            pd1 = pd.read_excel(filepath1, dtype={'运单号': str})
            pd2 = pd.read_excel(filepath2, dtype={'运单号': str})
            pdx0 = pd.DataFrame(px0).rename(columns={'名称': '承包商', '区域主管': '区域主管'})
            pd0 = pd.DataFrame(pd0)
            pd1 = pd.DataFrame(pd1)
            pd2 = pd.DataFrame(pd2)
            pdx = pd0.append(pd1).append(pd2).sort_values(by='区域主管').sort_values(by='承包商').sort_values(by='三级工单类型')
            pdx = pdx.drop_duplicates()
            path0 = "D:/ExcelData/工单明细" + str(time.time()) + ".xlsx"
            pdx.to_excel(path0, index=False)
            os.startfile(path0)
            px1_1 = pd.pivot_table(pdx, index='承包商', values='运单号', aggfunc=np.count_nonzero, margins=True, margins_name='总计').sort_values(by='运单号', ascending=False)
            path1 = "D:/ExcelData/倒数排名"+str(time.time())+".xlsx"
            px1_1.to_excel(path1)
            pcc = pd.DataFrame(pd.read_excel(path1))
            pcc = pd.merge(pcc, pdx0, how='left')
            data = {'承包商': pcc['承包商'],
                    '运单号': pcc['运单号'],
                    '区域主管': pcc['区域主管']}
            pcc = pd.DataFrame(data)
            pcc.to_excel(path1, index=False)
            os.startfile(path1)
            return True
        except Exception:
            return False


